﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAO;
using System.Configuration;
using Entidades;

namespace DAO
{
    public class CineDAO
    {
        //**********************insertar cine
        public static void Insertar(Cine cin, List<CinePorSala> cineSala)
        {
            List<Cine> cines = new List<Cine>();
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;

            string sql = "insert into Cine (nombre, direccion, numero, barrio, telefono) values (@nom, @dire, @num, @barrio, @tel); select @@identity;";
            
            cmd.Parameters.AddWithValue("@nom", cin.Nombre);
            cmd.Parameters.AddWithValue("@dire", cin.Direccion);
            cmd.Parameters.AddWithValue("@num", cin.NumDireccion);
            cmd.Parameters.AddWithValue("@barrio", cin.Barrio);
            cmd.Parameters.AddWithValue("@tel", cin.Telefono);
            
            try
            {
                cn.Open();                
                cmd.CommandText = sql;                
                cmd.CommandType = CommandType.Text;                
                cmd.ExecuteNonQuery();               
                SqlCommand cmd1 = new SqlCommand("select @@Identity", cn);
                cin.NumeroCine = Convert.ToInt32(cmd1.ExecuteScalar());

                foreach (CinePorSala cinePorSala in cineSala)
                {
                    cinePorSala.Id_Cine = cin.NumeroCine;
                    CinePorSalaDAO.insertar(cinePorSala);
                }                
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al insertar la pelicula:" + ex.Message);
            }
            finally
            {                
                cn.Close();
            }
        }

        //******************buscar por id

        public static Cine BuscarPorId(int idCine)
        {
            Cine cin = null;
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd1 = new SqlCommand();
            cmd1.Connection = cn;
            cin = new Cine();
            try
            {
                cn.Open();
                cmd1.CommandText = "select id_Cine,nombre,direccion,numero,barrio,telefono from Cine where id_Cine=@idCine";
                cmd1.Parameters.AddWithValue("@idCine", idCine);
                SqlDataReader dr = cmd1.ExecuteReader();
                while (dr.Read())
                {
                    cin.NumeroCine = int.Parse(dr["id_Cine"].ToString());
                    cin.Nombre = dr["nombre"].ToString();
                    cin.Direccion = dr["direccion"].ToString();
                    cin.NumDireccion = int.Parse(dr["numero"].ToString());
                    cin.Barrio = dr["barrio"].ToString();
                    cin.Telefono = int.Parse(dr["telefono"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al buscar el Cine:" + ex.Message);

            }
            finally
            {
                cn.Close();
            }    
            return cin;
        }

        public static void DarBaja(int idCine)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            try
            {
                cn.Open();
                cmd.CommandText = "delete from Cine where id_Cine=@id";
                cmd.Parameters.Add(new SqlParameter("@id", idCine));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Erroar al eliminar el Cine:" + ex.Message);

            }
            finally
            {
                cn.Close();
            } 
        }

        public static DataTable CargarGrilla(string orden)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                cmd.CommandText = "select id_Cine, nombre, direccion, numero, barrio, telefono from Cine";
                if (orden == "")
                    cmd.CommandText += " order by nombre";
                else
                {
                    cmd.CommandText += " order by " + orden;
                }
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar la grilla:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }            
            return dt;
        }

        public static DataTable cargarGrillaNombre(string nombre)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                cmd.CommandText = "select id_Cine, nombre, direccion, numero, barrio, telefono from Cine where nombre like @nom";
                cmd.Parameters.AddWithValue("@nom", "%" + nombre + "%");
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar la grilla:" + ex.Message);
            }
            finally
            {
                cn.Close();
            } 
            return dt;
        }
        public static void update(Cine c)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            try
            {
                cn.Open();
                cmd.CommandText = "update Cine set nombre=@nom,direccion=@dir,numero=@num,barrio=@bar,telefono=@tel where id_Cine=@Id";
                cmd.Parameters.AddWithValue("@Id", c.NumeroCine);
                cmd.Parameters.AddWithValue("@nom", c.Nombre);
                cmd.Parameters.AddWithValue("@dir", c.Direccion);
                cmd.Parameters.AddWithValue("@num", c.NumDireccion);
                cmd.Parameters.AddWithValue("@bar", c.Barrio);
                cmd.Parameters.AddWithValue("@tel", c.Telefono);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al actualizar el cine :" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }
    }
}
